package com.yjck.fm.util;


import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;
/**
 * @author yjck
 * 数据库操作工具类
 */
public  class DBUtil {
	private BasicDataSource dataSource;
	private PreparedStatement ps = null;
	private Statement st = null;
	public void setDataSource(BasicDataSource dataSource) {
		this.dataSource = dataSource;
	} 
	
	
	/**
	 * 得到数据库连接对象
	 * @return Connection
	 */
	public  Connection getConn(){	
		Connection conn = null;
		try {
		if(this.dataSource != null){
			conn = dataSource.getConnection();			
		}else{
			Properties properties = new Properties();
				try {
					properties.load(this.getClass().getClassLoader().getResourceAsStream("jdbc.properties"));
					String driver = properties.getProperty("jdbc.driverClassName");
					String url = properties.getProperty("jdbc.url");
					String user = properties.getProperty("jdbc.username");
					String password = properties.getProperty("jdbc.password");
					Class.forName(driver);
					conn = DriverManager.getConnection(url, user, password);
				} catch (IOException e) {
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
		
		}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 不传参数得到结果集
	 * @param sql
	 * @return ResultSet
	 */
	public  ResultSet getRs(String sql){
		ResultSet rs = null;
		try {
			this.st = getConn().createStatement();
			 rs =	st.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	/**
	 * 带参数的查询到结果集
	 * @param sql
	 * @param params
	 * @return ResultSet
	 */
	public  ResultSet getRs(String sql,Object[] params){
		ResultSet rs = null;
		try {
			this.ps = getConn().prepareStatement(sql);
			String[] splitstr = sql.split("\\?");
			for(int i = 0; i < params.length;i++){
				if(splitstr[i].toLowerCase().indexOf("like")>0){
					ps.setString(i+1, "%"+params[i].toString()+"%");
				}else{
					ps.setString(i+1, params[i].toString());	
				}
			}
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;	
	}
	/**
	 * 带参数查询记录数
	 * @param sql
	 * @param params
	 * @return Int
	 */
	public int getCount(String sql,Object[] params){
		ResultSet rs = null;
		int count = 0;
		try {
			this.ps = getConn().prepareStatement(sql);
			String[] splitstr = sql.split("\\?");
			for(int i = 0; i < params.length;i++){
				if(splitstr[i].toLowerCase().indexOf("like")>0){
					ps.setString(i+1, "%"+params[i].toString()+"%");
				}else{
					ps.setString(i+1, params[i].toString());					
				}
			}
			rs = ps.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	/**
	 * 不带参数查询记录数
	 * @param sql
	 * @param params
	 * @return Int
	 */
	public int getCount(String sql){
		int rows = 0;
		ResultSet rs = null;
		try {
			this.st = getConn().createStatement();
			rs = st.executeQuery(sql);
			rs.last();
			rows = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rows;
	}
	/**
	 * 执行更新方法，参数
	 * @param sql
	 * @param params
	 * @return
	 */
	public int execUpdate(String sql,Object[] params){
		int num = 0;
		try {
			this.ps = getConn().prepareStatement(sql);
			for(int i = 0; i < params.length;i++){
				ps.setString(i+1, params[i].toString());
			}
			num = this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return num;
	}
	
	public List<Object> getList(String sql,Object[] params,Object obj){
		ResultSet rs = null;
		if(params == null){
		   rs = this.getRs(sql);
		}else{
		   rs = this.getRs(sql, params);
		}
		List<Object> list = new ArrayList<Object>();
		try {
			while(rs.next()){
			int i = 1;
			Object inst =  obj.getClass().newInstance();
			Field[] fields = inst.getClass().getDeclaredFields();
				for(Field field : fields){
					String setStr = field.getName().substring(0, 1).toUpperCase();
					String setMehtodName = "set"+setStr+field.getName().substring(1);
					Method setMethod = obj.getClass().getMethod(setMehtodName, new Class[]{field.getType()});
					setMethod.invoke(inst, new Object[]{rs.getString(i)});
					i++;
				}
				list.add(inst);
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
		
	}
}
